<?php
/*!
	\file class.sql.php
	\brief Contains class definition for the SQL class.
*/
define(CONFIG_FN,  "SimpleDBO.cfg"); //!< Name of the config file that contains MySQL connection settings.
define(GLOBAL_DBH, "sql_dbh"); 
define(GLOBAL_DBN, "sql_dbname");

/*!
	\class SQL
	\author Alex McClung
	\date Created:  March 24, 2005
	\date Modified: March 25, 2005
	\brief MySQL PHP wrapper class.
	
	SQL is mainly a wrapper class, but also provides extra utility functions for
	accessing and using MySQL databases. Connection parameters can be passed in through
	to the constructor, otherwise they will be loaded using the Config class (the location
	of the config file is defined in inc.consts.php).
	
	This object purposely ignores the mysql_fetch_row, mysql_fetch_array, mysql_fetch_assoc
	functions.
	
	\sa Escape(), SearchForeignKeys()
*/
class SQL
{

	private $_db_host = "";   //!< database hostname
	private $_db_user = "";   //!< database username
	private $_db_name = "";	  //!< the database to use
	private $_result  = NULL; //!< result identifier returned from the most recent query
	
	static $_dbh;	//!< database connection id ("database handler" id)

	/*!
		\brief Class constructor.
		\param $host - name/ip address (and port, if necessary) of the MySQL host to connect to
		\param $user - SQL login name
		\param $pw   - SQL password
		\param $db   - name of the database to select
		\returns SQL Class instance
		\sa SwitchDB()
		
		Conects to the SQL database. If connection parameters are not passed in
		to the constructor, they will be loaded from the config file (see class
		description). If you pass all 4 connection parameters into the constructors
		to override the config file, otherwise they will be ignored.
	*/
	public function __construct($host=NULL, $user=NULL, $pw=NULL, $db=NULL)
	{
		// If the global SQL variables have been set already, use them. This way,
		// we don't have to do a bunch of extra crap.
		if(isset($GLOBALS[GLOBAL_DBH]))
		{
			$this->_dbh     = $GLOBALS[GLOBAL_DBH];
			$this->_db_name = $GLOBALS[GLOBAL_DBN];
		}
		else
		{
			// Did the user not pass in enough arguments?
			if(func_num_args() < 4)
			{
				// Parse the config file
				$c = parse_ini_file(CONFIG_FN, true);
				
				$this->_host	= $c['MySQL']['HOST'];
				$this->_user	= $c['MySQL']['USER'];
				$this->_db_name	= $c['MySQL']['DATABASE'];
				$pw		        = $c['MySQL']['PASSWORD']; // Don't store the password (for security)
				
				unset($c);
			}
			// They passed in all four parameters
			else
			{
				// Store the DB name for safe keeping
				$this->_db_name = $db;
			}
			
			// Connect to that DB. This will die if any of it fails.
			$this->Connect($this->_host, $this->_user, $pw);
			
			// So far so good, store the DB handler and the db name
			$GLOBALS[GLOBAL_DBH] = $this->_dbh;
			$GLOBALS[GLOBAL_DBN] = $this->_db_name;
		}
	}
	
	/*!
		\brief (re)Connects to the database
		\param $host - name/ip address (and port, if necessary) of the MySQL host to connect to
		\param $user - SQL login name
		\param $pw   - SQL password
		\returns nothing
	*/
	public function Connect($host, $user, $pw)
	{
		// Try and connect
		$this->_dbh = mysql_connect($host, $user, $pw) or 
			die("Could not connect to MySQL (Host: $host, Login: $user)");
		
		// Try and select the database
		mysql_select_db($this->_db_name, $this->_dbh) or 
			die("Could not select database '" . $this->_db_name . "'");
	}
	
	/*!
		\brief Disconnects from the MySQL database
		\returns Result of mysql_close function
	*/
	public function Disconnect()
	{
		return mysql_close($this->dbh);
	}
	
	/*!
		\brief Changes the database being used.
		\param $db - the name of the database to switch to.
		\returns boolean - the result of the mysql_select_db function call
	*/
	public function SwitchDB($db)
	{
		$this->_db_name = $db;
		return mysql_select_db($this->_db_name, $this->dbh);
	}
	
	/*!
		\brief Submits a MySQL query.
		
		The MySQL result ID is automatically stored as a class property every query, so storing
		it in a separate variable is not necessary (unless the result needs to be kept while
		another query is executed). Recommended usage:
		<pre>
		$sm->Query($query);
		$row = $sm->Object();
		</pre>
		\sa Object()
		\param $query - MySQL query (string) to be sent to the database.
		\returns MySQL resource ID on success, will return FALSE if query fails.
	*/
	public function Query($query)
	{
		$this->_result = mysql_query($query, $this->dbh);
		return $this->_result;
	}
	
	/*!
		\brief Wrapper function for PHP's mysql_fetch_object function.
		
		Returns a MySQL row object from a MySQL query.
		
		\sa Query(), GetArray
		\param $result - MySQL resource ID from a previous MySQL query (optional).
		\returns A MySQL row object.
		
	*/
	public function GetObject($result = NULL)
	{
		$result = ($result == NULL) ? $this->_result : $result;
		$row = mysql_fetch_object($result);
		
		return $row;
	}
	
	/*!
		\brief Wrapper function for PHP's mysql_fetch_row function.
		
		Returns a MySQL row array from a MySQL query.
		
		\sa Query()
		\param $result - MySQL resource ID from a previous MySQL query (optional).
		\returns A MySQL row array.
		
	*/
	public function GetArray($result = NULL)
	{
		$result = ($result == NULL) ? $this->_result : $result;
		$row = mysql_fetch_row($result);
		
		return $row;
	}
	
	/*!
		\brief Returns the most-recently inserted ID (only applies to tables with AUTO INCREMENT columns as their primary key)
		\returns int
	*/
	public function LastID()
	{
		return mysql_insert_id();
	}
	
	/*!
		\brief Frees result memory
		\returns True on success, false on failure
	*/
	public function Free($result=NULL)
	{
		if($result==NULL) $result = $this->_result;
		return mysql_free_result($result);
	}
	
	/*!
		\brief Returns the last error raised by MySQL
		\returns String (error message)
	*/
	function LastError($showNum=TRUE)
	{
		$msg = ($showNum) ? mysql_errno($this->dbh) . ": " : "";
		$msg .= mysql_error($this->dbh);
		
		return $msg;
	}

	/*!
		\brief Retrieves a list of the tables in the currently selected database.
		\returns An array of strings.
	*/
	function Tables()
	{
		$list = mysql_list_tables($this->_db_name);
		$numTables = mysql_num_rows($list);
		for($i=0; $i<$numTables; $i++)
			$tableNames[] = mysql_tablename($list, $i);
		return $tableNames;
	}
	
	/*!
		\brief determines whether or not a table exists in the database.
		\param $table - the name of the table to check
		\returns true if $table exists, false if not
	*/
	function TableExists($table)
	{
		return !(@array_search($table, $this->Tables())===FALSE);
	}
	
	/*!
		\brief Returns an array containing the column names for a table
		
		This function returns only the names of the columns in a table, not their
		column types, primary key status, etc.
		
		\param $table - the name of the table to describe
		\returns an array of strings
	*/
	function Columns($table)
	{
		$this->Query("DESCRIBE $table");
		
		$cols = array();
		
		while($r = $this->GetObject())
			$cols[] = $r->Field;
		
		return $cols;
	}
	
	/*!
		\brief Retrieves column info for a tble
		\param $table - the table to get info for
		\param $column - optionally, you can choose to describe only one column in the table by passing in the name of that column
		\returns array
		
		Retrieves detailed column info on a table in an array. One column per array element is described. The
		description itself is a string-indexed array with the following indexes:
		<pre>
		'name' - the name of the column
		'type' - the column type (INT, VARCHAR, etc)
		'attributes' - If present, will be one of the following: binary, unsigned, unsigned zerofill
		'key' - If the column is a key type, this will be either 'PRI' (primary key), 'UNI' (unique), or 'MUL' (index)
		'null' - The null/not null status of the column
		'default' - if present, this will contain the column's default value
		'extra' - Extra information about the column, for example 'auto_increment'
		</pre>
		Additionally, depending on the column type, the following may be present in the column's description
		<pre>
		'elements' - For ENUM or SET column types - contains a list of values that the column can contain
		'length' - For pretty much every other column type - the maximum length of the column (if present)
		</pre>
	*/
	function Describe($table, $column=NULL)
	{
		$query = "SHOW COLUMNS FROM `$table`";
		if($column != NULL)
			$query .= " LIKE '$column'";
		else
			$rows = array();
			
		$this->Query($query);
		
		while($row = $this->GetObject())
		{
			$thisRow = array();

			list($type, $fieldinfo, $attributes) = $this->GetTypeInfo($row->Type);
			
			$thisRow['name'] = $row->Field;
			$thisRow['type'] = $type;
			if($type == "ENUM" || $type =="SET")
				$thisRow['elements'] = split(",", str_replace("'", "", $fieldinfo));
			else
				$thisRow['length'] = $fieldinfo;
			$thisRow['attributes'] = $attributes;
			$thisRow['key'] = $row->Key;
			$thisRow['null'] = $row->Null;
			$thisRow['default'] = $row->Default;
			$thisRow['extra'] = $row->Extra;
			
			if($column == NULL)
				$rows[] = $thisRow;
			else
				$rows = $thisRow;
		}
		
		return $rows;
	}
	
	/*!
		\brief Retrieves a table's comments
		\returns String
	*/
	function GetComment($table)
	{
		$this->Query("SHOW TABLE STATUS WHERE Name='$table'");
		$row = $this->GetObject();
		return $row->Comment;
	}
	
	/*!
		\brief Wrapper for PHP's mysql_num_rows function.
		
		Counts the number of rows in $result.
		\param $result - the resource identifier from a mysql query to count rows for (optional)
		\returns The number of rows in $result (int).
	*/
	function NumRows($result = "")
	{
		if($result == "") $result = $this->_result;
		return @(int)mysql_num_rows($result);
	}
	
	/*!
		\brief Wrapper function for mysql_data_seek
		\param $p - position to seek to
		\param $result - mysql resource id of a previous query
		\returns nothing
	*/
	function Seek($p=0, $result="")
	{
		if($result == "") $result = $this->_result;
		mysql_data_seek($result, $p);
	}
	
	/*!
		\brief Determines if a MySQL query produced an empty result.
		\sa numRows()
		\param $result - MySQL resource ID of a previous query
		\returns true if the result was empty, false if not

		Uses the numRows function to count how many rows were produced in the last
		(or supplied) MySQL query.
		
	*/
	function IsEmpty($result = "")
	{
		if($result == "") $result = $this->_result;
		if($this->NumRows($result) <= 0)
			return true;
		return false;
	}
	
	/*!
		\brief Deletes a table from the database.
		\returns nothing
	*/
	function DropTable($table)
	{
		return $this->Query("DROP TABLE IF EXISTS $table");
	}
	
	/*!
		\brief Empties a table of all its data
		\returns nothing
	*/
	function EmptyTable($table)
	{
		return $this->Query("TRUNCATE TABLE $table");
	}
	
	/*!
		\brief Optimizes a table
		\returns nothing
	*/
	function OptimizeTable($table)
	{
		return $this->Query("OPTIMIZE TABLE `$table`");
	}
	
	/*!
		\brief Searches the database for foreign keys.

		Searches the currently selected database for a table whose PRIMARY KEY
		matches the first parameter. 
		
		\param $columnToFind - the column name to search for
		\param $tableToIgnore - the table to ignore in the search
		\returns Name of the table containing $columnToFind, false if it was not found
	*/
	function SearchForPrimaryKey($columnToFind, $tableToIgnore = "")
	{
		// preserve old result variable
		$oldResult = $this->_result;
		
		// query the database for the list of tables in the current database
		$listOfTables = $this->Query("SHOW TABLES");
		
		$column = "Tables_in_" . $this->_db_name;
		// go through all of the tables returned
		while($table = $this->GetObject($listOfTables))
		{
			// store the name of the current table being analyzed
			$thisTable = $table->$column;
			// if the current table is not the table to ignore,
			// search for $columnToFind
			if($thisTable != $tableToIgnore) 
			{
				// this query will be empty unless $columnToFind is in this table
				$this->Query("DESCRIBE $thisTable '$columnToFind'");
				// if the result is not empty, see if the column is a primary key
				if(!$this->isEmptyResult())
				{
					$row = $this->GetObject();
					if($row->Key == "PRI")
					{
						// first restore old result identifier
						$this->_result = $result;
						// now return the table name
						return $thisTable;
					}
				}
			}
		}
		// no table name found, so just return false
		// after restoring the result id
		$this->_result = $oldResult;
		return false;
	}
	
	/*!
		\brief Returns the primary key for a table
		\param $table - the name of the table to get the primary key for
		\returns the name of the primary key in the table (string)
	*/
	function GetPrimaryKey($table)
	{
		$this->Query("DESCRIBE $table");
		while($row = $this->GetObject())
			if($row->Key == "PRI")
				return $row->Field;
	}
	
	/*!
		\brief Returns column type and length / extra information
		
		Uses regular expressions to pull out MySQL column type and any extra information
		from a column's description ('Type').
		
		Example input: <code>getTypeInfo("INT(11) UNSIGNED")</code><br>
		Example return: <code>array("INT", "11", "UNSIGNED")</code>
		
		Example input: <code>getType("ENUM('y','n')")</code><br>
		Example return: <code>array("ENUM", "'y','n'")</code>
		
		\param $fieldType - SQL Description of a column's field type
		\returns array($type, $fieldInfo)
	*/
	function GetTypeInfo($fieldType)
	{
		// pull out the field type and any kind of field information
		preg_match("/([a-z]+)\((.+)\) ?([a-z ]+)?/i", $fieldType, $params);
		// if the preg matches nothing then the field type probably doesn't have any
		// length information, so default it to whatever MySQL says the Type is
		$type = ($params[1] == "") ? strtoupper($fieldType) : strtoupper($params[1]);
		$fieldInfo = $params[2]; // and keep the field length/definition data
		$attributes = trim($params[3]);
		
		return array($type, $fieldInfo, $attributes);
	}
	
	/*!
		\brief Returns a string that represents a PHP data type from a MySQL field type.
		
		Given a MySQL field type (SET, FLOAT, DOUBLE, DECIMAL, TINYINT, SMALLINT, MEDIUMINT,
		BIGINT, INT, YEAR, etc...), this function will return an appropriate PHP data type.
		For example, SET would return "array", TINYINT would return "int", and so on.
		
		\param $type - MySQL field type (i.e. "INT, or VARCHAR(255)")
		\returns String representing a PHP data type: "array", "double", "int", or "string"
	*/
	function GetFieldType($type)
	{
		preg_match("/([a-zA-Z_\-0-9]+)\(([0-9a-zA-Z\,']+)\)/", $type, $params);
		$type = ($params[1] == "") ? strtoupper($type) : strtoupper($params[1]);

		switch($type)
		{
			case "SET":
			case "ENUM": return "array"; break;
			
			case "FLOAT": 
			case "DOUBLE": 
			case "DECIMAL": return "double"; break;
			
			case "TINYINT": 
			case "SMALLINT": 
			case "MEDIUMINT":
			case "BIGINT": 
			case "INT": 
			case "YEAR": return "integer"; break;
		}
		
		return "string";
	}

	/*!
		\brief Safely escapes a string (will not re-escape an already escaped string).
		
		\param $string - the string to escape
		\param $escape_nl - if true, this function will escape newline characters (defaults to true)
		\returns An escaped version of $string
	*/
	function Escape($string, $escape_nl=true)
	{
		// fix newline characters first
		$string = str_replace("\r\n", "\n", $string);
		
		// Look for non-escaped quotes
		if(!preg_match("/(\\\'|\\\\\")/", $string))
		{
			$escaped = mysql_real_escape_string($string);
			if($escape_nl)
				$escaped = str_replace("\\n", "\n", $escaped);
		}
		else
			$escaped = $string;
		
		return $escaped;
	}
	
	/*!
		\brief Custom get function - allows read-only access of member variables
		\returns mixed
		
		Does NOT provide read-only access to the db password.
	*/
	public function __get($v)
	{
		switch($v)
		{
			case "host": 	return $this->_host; 	break;
			case "user": 	return $this->_user;	break;
			case "db": 		return $this->_db_name; break;
			case "dbh":	 // This is mainly used by this class. Rather than using $this->_dbh, using $this->dbh will first test 
						 // to make sure that _dbh is valid, if it isn't, it will call Connect (to reconnect to the databse)
					if(!$this->_dbh)
						$this->Connect();
					return $this->_dbh;
				break;
			case "result":	return $this->_result;	break;
		}
		
		return $this->$v;
	}
}
?>